package com.common.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class ExcelUtil {
	
	protected static Logger log = Logger.getLogger(ExcelUtil.class);
	
	//取得sheet第rownum+1行列名为columnname的值
	public static String getCellValue(String path, String sheetName, int rownum, String columnname) throws Exception{
    	InputStream is = null;
    	HSSFWorkbook workbook = null;
    	try {
    		is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
        Sheet sheet = workbook.getSheet(sheetName);
		Row row = sheet.getRow(rownum);
		int columnnum = getcolumnNumByColumnName(path, sheetName, columnname);
		String cellvalue = row.getCell(columnnum).getStringCellValue();
		try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cellvalue;
	}

	//在excel文件某行对应的某列名赋值
	public static void writeExcel(String path, String sheetName, int rownum, String columnname, String str) throws Exception{
		InputStream is = null;
		HSSFWorkbook workbook = null;
    	Sheet sheet = null;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheet(sheetName);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Row row = sheet.getRow(rownum);
		int columnnum = getcolumnNumByColumnName(path, sheetName, columnname);
		Cell cell = row.getCell(columnnum);
		cell.setCellValue(str);
		OutputStream out = null;
		try {
			out = new FileOutputStream(path);
			workbook.write(out);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//在excel文件具体某行数某列数赋值
	public static void writeExcel(String path, String sheetName, int rownum, int columnnum, String str){
		InputStream is = null;
		HSSFWorkbook workbook = null;
    	Sheet sheet = null;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheet(sheetName);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Row row = sheet.getRow(rownum);
		Cell cell = row.getCell(columnnum);
		cell.setCellValue(str);
		OutputStream out = null;
		try {
			out = new FileOutputStream(path);
			workbook.write(out);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//根据列数和值定位该值在该列的哪一行
	public static int getRowOfColumnBycellValue(String path, String sheetName, String columnname, String cellvalue) throws Exception{
		int rownum = 0;
		InputStream is = null;
		HSSFWorkbook workbook = null;
    	Sheet sheet = null;
    	boolean isfound = false;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheet(sheetName);
			int numberOfRows = sheet.getLastRowNum() + 1;
			int columnnum = getcolumnNumByColumnName(path, sheetName ,columnname);
			for(int i=1;i<numberOfRows;i++){
				Row row = sheet.getRow(i);
				if(row.getCell(columnnum).getStringCellValue().equals(cellvalue)){
					isfound = true;
					rownum = i;
					break;
				}
			}
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(!isfound){
			handlefailure("相应列[" + columnname + "]找不到对应值[" + cellvalue + "]");
		}
		return rownum;
	}
	
	//根据列数和值定位该值在该列的哪一行
	public static int getRowOfColumnBycellValue(String path, String sheetName, int columnnum, String cellvalue) throws Exception{
		int rownum = 0;
		InputStream is = null;
		HSSFWorkbook workbook = null;
    	Sheet sheet = null;
    	boolean isfound = false;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
			sheet = workbook.getSheet(sheetName);
			int numberOfRows = sheet.getLastRowNum() + 1;
			for(int i=1;i<numberOfRows;i++){
				Row row = sheet.getRow(i);
				if(row.getCell(columnnum).getStringCellValue().equals(cellvalue)){
					isfound = true;
					rownum = i;
					break;
				}
			}
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(!isfound){
			handlefailure("第" + (columnnum+1) + "列找不到对应值[" + cellvalue + "]");
		}
		return rownum;
	}
	
	public static boolean iscolumnnameexists(String path, String sheetName, String columnname){
		boolean isfound = false;
		InputStream is = null;
		HSSFWorkbook wbs = null;
		Sheet sheet = null;
		try {
			is = new FileInputStream(path);
			wbs = new HSSFWorkbook(is);
			sheet = wbs.getSheet(sheetName);
			Row row0 = sheet.getRow(0);
			int firstrowcount = countNonEmptyColumns(sheet);
			for(int i=0;i<firstrowcount;i++){
				if(row0.getCell(i).getStringCellValue().equals(columnname)){
					isfound = true;
					break;
				}
			}
			wbs.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isfound;
	}
	
	//根据excel第一行的columnname得到是第几列
	public static int getcolumnNumByColumnName(String path, String sheetName, String columnname) throws Exception{
		InputStream is = null;
		HSSFWorkbook wbs = null;
		Sheet sheet = null;
		int index = 0;
		boolean isfound = false;
		try {
			is = new FileInputStream(path);
			wbs = new HSSFWorkbook(is);
			sheet = wbs.getSheet(sheetName);
			Row row0 = sheet.getRow(0);
			int firstrowcount = countNonEmptyColumns(sheet);
			for(int i=0;i<firstrowcount;i++){
				if(row0.getCell(i).getStringCellValue().equals(columnname)){
					isfound = true;
					index = i;
					break;
				}
			}
			wbs.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(!isfound){
			handlefailure("找不到相应的列名[" + columnname + "]");
		}
		return index;
	}
	
    public static Map<String, Map<String, String>> generatemapOfMap(String path, String sheetName, String columnname) throws Exception{
    	InputStream is = null;
    	HSSFWorkbook workbook = null;
    	Sheet sheet = null;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sheet = workbook.getSheet(sheetName);
    	int numberOfColumns = countNonEmptyColumns(sheet);
    	int numberOfRows = sheet.getLastRowNum() + 1;
    	Map<String, Map<String, String>> mapofdataMap = new HashMap<String, Map<String, String>>();
    	Row row0 = sheet.getRow(0);
    	List<String> firstrowcellLst = getRowCellLst(row0);
    	if(!firstrowcellLst.contains(columnname)){
    		handlefailure("参数表没有[" + columnname + "]这列名");
    	}
    	int index = 0;
    	for(int firstrowcolumn=0;firstrowcolumn<numberOfColumns;firstrowcolumn++){
    		if(row0.getCell(firstrowcolumn).equals(columnname)){
    			index = firstrowcolumn;
    			break;
    		}
    	}
    	for(int rowNum=1;rowNum<numberOfRows;rowNum++) {
    		Row row = sheet.getRow(rowNum);
    		Map<String, String> datamap = new HashMap<String, String>();
    		if(isEmpty(row)){
                break;
            }else{
            	for(int column=0;column<numberOfColumns;column++){
            		datamap.put(firstrowcellLst.get(column), row.getCell(column).getStringCellValue());
            		datamap.remove(columnname);
            	}
            }
    		mapofdataMap.put(row.getCell(index).getStringCellValue(), datamap);
    	}
        try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return mapofdataMap;
    }
    
    public static Map<String, Map<String, String>> generatemapOfMap(String path, String sheetName, String keycolumnname, String[] columnnameLst) throws Exception{
    	InputStream is = null;
    	HSSFWorkbook workbook = null;
    	Sheet sheet = null;
		try {
			is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sheet = workbook.getSheet(sheetName);
    	int numberOfColumns = countNonEmptyColumns(sheet);
    	int numberOfRows = sheet.getLastRowNum() + 1;
    	Map<String, Map<String, String>> mapofdataMap = new HashMap<String, Map<String, String>>();
    	Row row0 = sheet.getRow(0);
    	List<String> firstrowcellLst = getRowCellLst(row0);
    	if(!firstrowcellLst.contains(keycolumnname)){
    		handlefailure("参数表没有[" + keycolumnname + "]这列名");
    	}
    	int index = 0;
    	for(int firstrowcolumn=0;firstrowcolumn<numberOfColumns;firstrowcolumn++){
			log.info("firstrowcolumn " + firstrowcolumn);
			log.info("row0.getCell(firstrowcolumn) " + row0.getCell(firstrowcolumn).getStringCellValue());
    		if(row0.getCell(firstrowcolumn).getStringCellValue().equals(keycolumnname)){
    			index = firstrowcolumn;
    			break;
    		}
    	}
    	log.info("[" + keycolumnname + "]在第" + index + "列");
    	for(int rowNum=1;rowNum<numberOfRows;rowNum++) {
    		Row row = sheet.getRow(rowNum);
    		Map<String, String> datamap = new HashMap<String, String>();
    		if(isEmpty(row)){
                break;
            }else{
            	for(int column=0;column<numberOfColumns;column++){
            		datamap.put(firstrowcellLst.get(column), row.getCell(column).getStringCellValue());
            		for(String columnname : columnnameLst){
            			datamap.remove(columnname);
            		}
            	}
            }
    		mapofdataMap.put(row.getCell(index).getStringCellValue(), datamap);
    	}
        try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return mapofdataMap;
    }
    
    //把第一行的内容除了某列名都放进List中
    public static List<String> getParamColumnNameLst(String path, String sheetName, String columnname){
    	InputStream is = null;
    	HSSFWorkbook workbook = null;
    	try {
    		is = new FileInputStream(path);
			workbook = new HSSFWorkbook(is);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
        Sheet sheet = workbook.getSheet(sheetName);
        int numberOfColumns = countNonEmptyColumns(sheet);
        List<String> firstrowcelllst = new ArrayList<String>();
        Row row0 = sheet.getRow(0);
        for(int i=0;i<numberOfColumns;i++){
        	if(!row0.getCell(i).getStringCellValue().equals(columnname)){
        		firstrowcelllst.add(row0.getCell(i).getStringCellValue());
        	}
        }
		try {
			workbook.close();
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return firstrowcelllst;
    }
    
    //得到该行的每列数据，放进一个List里
    private static List<String> getRowCellLst(Row cells){
    	List<String> rowcelllst = new ArrayList<String>();
    	int numberOfColumns = firstEmptyCellPosition(cells);
    	for(int i=0;i<numberOfColumns;i++){
    		rowcelllst.add(cells.getCell(i).getStringCellValue());
    	}
    	return rowcelllst;
    }
    
    private static boolean isEmpty(Row row) {
        // TODO Auto-generated method stub
        Cell firstCell = row.getCell(0);
        boolean rowIsEmpty = (firstCell == null)
                || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK);
        return rowIsEmpty;
    }
	
    //统计第一行有效列数
    private static int countNonEmptyColumns(Sheet sheet) {
        // TODO Auto-generated method stub
        Row firstRow = sheet.getRow(0);
        return firstEmptyCellPosition(firstRow);
    }
	
	//取得excel某行的有效列数(最后一个不为空的列数)
    private static int firstEmptyCellPosition(Row cells) {
        // TODO Auto-generated method stub
        int columnCount = 0;
        for (Cell cell : cells) {
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }
            columnCount++;
        }
        return columnCount;
    }
    
    //报出错误，对应案例置为fail
    private static void handlefailure(String msg) throws Exception{
    	throw new Exception(msg);
    }
}
